package com.kai.runner;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLOrderBy;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLMethodInvokeExpr;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.parser.ParserException;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.kai.pojo.ColumnMap;
import com.kai.pojo.DataServiceApiSetting;
import com.kai.pojo.TableDetail;
import com.kai.service.ColumnMapService;
import com.kai.service.DataServiceApiSettingService;
import com.kai.service.TableDetailService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 *
 *
 * @author pankx
 * @date 2023/11/3 11:20
 */
@Component
public class StartRunner implements CommandLineRunner {

    Logger log = LoggerFactory.getLogger(StartRunner.class);

    @Resource
    private DataServiceApiSettingService dataServiceApiSettingService;

    @Resource
    private ColumnMapService columnMapService;

    @Resource
    private TableDetailService tableDetailService;

    @Resource
    private ObjectMapper objectMapper;

    @Override
    public void run(String... args) throws Exception {

        log.info("启动");
        // api接口
        List<DataServiceApiSetting> list = dataServiceApiSettingService.list(new LambdaQueryWrapper<DataServiceApiSetting>().orderByAsc(DataServiceApiSetting::getApiId));
        String s = objectMapper.writeValueAsString(list);
        log.info("api 接口数据：{}", s);

        // 字段映射关系
        List<ColumnMap> columnMapList = columnMapService.list();
        log.info("size：{}，字段表映射数据：{}", columnMapList.size(), objectMapper.writeValueAsString(columnMapList));
        MultiValueMap<String, String> columnMaps = new LinkedMultiValueMap<>();
        columnMapList.forEach(e -> columnMaps.add(e.getOldColumn(),e.getNewColumn()));
        log.info("size：{}，转换成 map 后的数据：{}", columnMaps.keySet().size(),columnMaps);

        // 表名映射关系
        List<TableDetail> tableDetails = tableDetailService.list();
        Map<String, String> tableMaps = tableDetails.stream().
                collect(Collectors.toMap(TableDetail::getOldTableName, TableDetail::getNewTableName));
        log.info("表名映射关系：{}", tableMaps);


        for (DataServiceApiSetting e : list) {
            String sql = SQLUtils.formatMySql(e.getSqlTemplate());

            try {
                SQLSelectStatement sqlStatement = (SQLSelectStatement)SQLUtils.parseSingleMysqlStatement(e.getSqlTemplate());
                SQLSelectQueryBlock select = (SQLSelectQueryBlock)sqlStatement.getSelect().getQuery();
                // select 查询字段
                List<SQLSelectItem> selectList = select.getSelectList();

                // 表名
                SQLTableSource from = select.getFrom();

                // 条件
                SQLBinaryOpExpr where = (SQLBinaryOpExpr)select.getWhere();


                // ========== 替换新字段 ==========
                List<SQLSelectItem> sqlSelectItems = new ArrayList<>();
                for (SQLSelectItem sqlSelectItem : selectList) {
                    // SQLIdentifierExpr 没有单引号
                    String oldColumn = sqlSelectItem.toString();
                    String newColumn = columnMaps.getFirst(oldColumn);
                    if (StringUtils.hasText(newColumn)){
                        SQLIdentifierExpr expr = new SQLIdentifierExpr(newColumn);
                        SQLSelectItem item = new SQLSelectItem(expr);
                        item.setAlias(oldColumn);
                        sqlSelectItems.add(item);
                    } else {
                        sqlSelectItems.add(sqlSelectItem);
                    }

                }
                select.getSelectList().clear();
                select.getSelectList().addAll(sqlSelectItems);
                // ========== 替换新字段 ==========

                // ========== 替换表名 ==========
                if (from instanceof SQLExprTableSource){

                    SQLExpr expr = ((SQLExprTableSource) from).getExpr();

                    // 有数据库名，有表名。例子：x.x
                    if (expr instanceof SQLPropertyExpr){
                        SQLExpr owner = ((SQLPropertyExpr) expr).getOwner();
                        String oldTableName = ((SQLPropertyExpr) expr).getName();
                        String newTableName = tableMaps.get(oldTableName);
                        if (StringUtils.hasText(newTableName)){
                            ((SQLPropertyExpr) expr).setName(newTableName);
                        }

                    }

                    // 只有表名字，没有数据库名。例子：x
                    if (expr instanceof SQLIdentifierExpr){
                        String oldTableName = ((SQLIdentifierExpr) expr).getName();
                        String newTableName = tableMaps.get(oldTableName);
                        if (StringUtils.hasText(newTableName)){
                            ((SQLIdentifierExpr)expr).setName(newTableName);
                        }
                    }
                }
                // ========== 替换表名 ==========

                // ========== 替换 where 条件 ==========
                if (where != null){
                    updateWhereColumn(where, columnMaps);
                }
                // ========== 替换 where 条件 ==========


                // ========== 替换 group by ==========
                SQLSelectGroupByClause groupBy = select.getGroupBy();
                if (groupBy != null){
                    for (SQLExpr item : groupBy.getItems()) {
                        if (item instanceof SQLIdentifierExpr){
                            String oldColumn = ((SQLIdentifierExpr) item).getName();
                            String newColumn = columnMaps.getFirst(oldColumn);
                            if (StringUtils.hasText(newColumn)){
                                ((SQLIdentifierExpr) item).setName(newColumn);
                            }
                        } else {
                            log.error("其他情况的 group by，sql：{}，id：{}", sql, e.getApiId());
                        }
                    }
                }
                // ========== 替换 group by ==========


                // ========== 替换order by ==========
                SQLOrderBy orderBy = select.getOrderBy();
                if (orderBy != null){
                    List<SQLSelectOrderByItem> items = orderBy.getItems();
                    for (SQLSelectOrderByItem item : items) {
                        SQLExpr expr = item.getExpr();
                        updateOrderByColumn(expr, columnMaps);
                    }
                }
                // ========== 替换order by ==========


                log.warn("修改前：\n{}",sql);
                log.warn("修改后：\n{}",select);
            } catch (ParserException exception){
                log.error("不能转化的sql：{}，记录id：{}", e.getSqlTemplate(), e.getApiId());
            }
        }

    }

    /**
     * 替换 where 条件字段
     * @param where 条件对象
     * @param columnMaps 新旧字段的映射关系
     */
    private void updateWhereColumn(SQLBinaryOpExpr where, MultiValueMap<String, String> columnMaps) {
        SQLExpr left = where.getLeft();
        if (left instanceof SQLIdentifierExpr){
            String old = ((SQLIdentifierExpr) left).getName();
            String newColumn = columnMaps.getFirst(old);
            if (newColumn != null){
                ((SQLIdentifierExpr) left).setName(newColumn);
            }
        } else {
            updateOrderByColumn(left, columnMaps);
            updateOrderByColumn(where.getRight(), columnMaps);
        }
    }

    /**
     * 替换 order by 条件字段
     * @param expr order by 对象
     * @param columnMaps 新旧字段的映射关系
     */
    private void updateOrderByColumn(SQLExpr expr, MultiValueMap<String, String> columnMaps){
        if (expr instanceof SQLIdentifierExpr){
            String old = ((SQLIdentifierExpr) expr).getName();
            String newColumn = columnMaps.getFirst(old);
            if (newColumn != null){
                ((SQLIdentifierExpr) expr).setName(newColumn);
            }
        }
        if (expr instanceof SQLBinaryOpExpr){
            SQLExpr left = ((SQLBinaryOpExpr) expr).getLeft();
            if (left instanceof SQLIdentifierExpr){
                String old = ((SQLIdentifierExpr) left).getName();
                String newColumn = columnMaps.getFirst(old);
                if (newColumn != null){
                    ((SQLIdentifierExpr) left).setName(newColumn);
                }
            } else {
                updateOrderByColumn(left, columnMaps);
            }
        }
        if (expr instanceof SQLMethodInvokeExpr){
            List<SQLExpr> arguments = ((SQLMethodInvokeExpr) expr).getArguments();
            for (SQLExpr argument : arguments) {
                if (argument instanceof SQLMethodInvokeExpr){
                    updateOrderByColumn(argument, columnMaps);
                }
                if (argument instanceof SQLIdentifierExpr){
                    String old = ((SQLIdentifierExpr) argument).getName();
                    String newColumn = columnMaps.getFirst(old);
                    if (newColumn != null){
                        ((SQLIdentifierExpr) argument).setName(newColumn);
                    }
                }
            }
        }
    }

    /**
     * 获取 where 条件的字段
     * @param where where条件内容
     * @return List<String>
     */
    public List<String> getWhereColumn(SQLBinaryOpExpr where){
        List<String> list = new ArrayList<>();
        SQLExpr left = where.getLeft();
        SQLExpr right = where.getRight();
        if (left instanceof SQLBinaryOpExpr && right instanceof SQLBinaryOpExpr){
            list.addAll(getWhereColumn((SQLBinaryOpExpr)left));
            list.addAll(getWhereColumn((SQLBinaryOpExpr)right));
        } else {
            list.add(left.toString());
        }

        return list;
    }
}
